﻿CREATE PROCEDURE [dbo].[spCreateTransactions] ( @OwnerID int, @AsOf smalldatetime )
AS
	set nocount on

	declare @RetryDelay int
	declare @RetryCount int
	declare @AllowPartialShipments bit
	declare @TransactionID int
	declare @AccountID int
	declare @ProcessorCode char(1)
	declare @PreferredProcessorCode char(1)

	select @RetryDelay = RetryCardDelay, 
		@RetryCount = RetryCardCount, 
		@AllowPartialShipments = AllowPartialShipments,
		@PreferredProcessorCode = ProcessorCode
	from tblOwner 
	where OwnerID = @OwnerID

	-- Cancel orders which have been retried too many times.
	update oh set OrderStatusCode = 'C', CanceledReasonID = 2, CanceledDate = GETDATE()
	from tblOrderHeader oh
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and ( select MAX(ts.SubmissionCount) from tblTransaction tr
				join tblTransactionSubmit ts on tr.TransactionID = ts.TransactionID
			where OrderID = oh.OrderID
				and tr.TransactionTypeCode = 'B'
				and tr.TransactionStatusCode = 'F'
				and ts.TransactionSubmitTypeCode = 'D'
			) >= @RetryCount

	-- Flag peding transactions for canceled, backordered or held orders as canceled.
	update tr set TransactionStatusCode = 'X'
	from tblOrderHeader oh
		join tblTransaction tr on oh.OrderID = tr.OrderID
	where oh.OrderStatusCode in ( 'C', 'B', 'H' )
		and tr.TransactionStatusCode in ( 'A', 'E' )


	-- Allocate inventory for pending orders
	declare aic cursor for select oh.OrderID -- AllocateInventoryCursor
	from tblOrderHeader oh
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and floor(cast(oh.ProjShipDate as float)) <= FLOOR(cast(@AsOf as float))
	declare @OrderID int
	open aic
	fetch next from aic into @OrderID
	while @@FETCH_STATUS = 0
	begin
		exec spAllocateInventory @OrderID, @AllowPartialShipments

		fetch next from aic into @OrderID
	end
	close aic
	deallocate aic

	-- Update the order status to Paid for orders which have an order total of 0.
	update oh set OrderStatusCode = 'E'
	from tblOrderHeader oh
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and floor(cast(oh.ProjShipDate as float)) <= FLOOR(cast(@AsOf as float))
		and oh.OrderTotal = 0

	-- Create bill transaction for pending orders that don't already have a transaction created for them.
	insert into tblTransaction ( TransactionTypeCode, Amount, CreateDate, TransactionStatusCode, AccountID, BankCheckNumber, OrderID, ProcessorCode )
	select 'B', OrderTotal, GETDATE(), 'E', oh.AccountID, oh.BankCheckNumber, oh.OrderID,
		coalesce(oh.ProcessorCode, @PreferredProcessorCode )
	from tblOrderHeader oh
		left outer join tblTransaction tr on oh.OrderID = tr.OrderID
			and tr.TransactionTypeCode = 'B' 
			and tr.TransactionStatusCode in ( 'E', 'A', 'I' )
	where oh.OrderStatusCode = 'P'
		and oh.OwnerID = @OwnerID
		and floor(cast(oh.ProjShipDate as float)) <= FLOOR(cast(@AsOf as float))
		and OrderTotal > 0
		and tr.OrderID is null
		and oh.AccountID is not null

	declare rtc cursor for -- refund transaction cursor
	select r.OrderID, round(sum(r.Cost), 2)
	from tblRefund r
		join tblOrderHeader oh on r.OrderID = oh.OrderID
	where r.TransactionID is null
		and oh.OwnerID = @OwnerID
	group by r.OrderID
	having round(sum(r.Cost), 2) > 0
	declare @Cost money
	open rtc
	fetch next from rtc into @OrderID, @Cost
	while @@FETCH_STATUS = 0
	begin
		declare @relatedTransactionID int
		set @AccountID = null

		select top 1 @AccountID = t.AccountID, @ProcessorCode = t.ProcessorCode, @relatedTransactionID = t.TransactionID
		from tblTransaction t
		where OrderID = @OrderID
			and TransactionTypeCode = 'B'
			and TransactionStatusCode = 'P'

		-- If we can't find a matching bill transaction then take the accountID from the order.
		-- This is necessary because imported orders don't always have a record of the payment.
		if @AccountID is null
			select @AccountID = AccountID from tblOrderHeader where OrderID = @OrderID

		if @AccountID is not null
		begin
			if @ProcessorCode is null
				set @ProcessorCode = @PreferredProcessorCode

			insert into tblTransaction ( TransactionTypeCode, Amount, CreateDate, TransactionStatusCode, AccountID, OrderID, ProcessorCode, RelatedTransactionID )
			select 'R', @Cost, GETDATE(), 'E', @AccountID, @OrderID, @ProcessorCode, @relatedTransactionID
			where @ProcessorCode is not null

			set @TransactionID = SCOPE_IDENTITY()
		
			update tblRefund set TransactionID = @TransactionID
			where OrderID = @OrderID
				and TransactionID is null
		end

		fetch next from rtc into @OrderID, @Cost
	end
	close rtc
	deallocate rtc

RETURN 0